﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using System.Data;



namespace BLL
{
    public class Operation  //业务逻辑类
    {
        public Operation()
        {
            C_DBAccess.connstr = "Data Source=192.168.2.252;" +
                                         "Initial Catalog=HUAWEI;" +
                                         "Persist Security Info=True;" +
                                         "User ID=xpl;Password=20030102";
        }

        public Operation(string connstr)
        {
            C_DBAccess.connstr = connstr;
        }
        
       
        /// <summary>
        /// 登陆函数
        /// </summary>
        /// <param name="username">用户账号</param>
        /// <param name="userpwd">用户密码</param>
        /// <returns>判断登陆是否成功</returns>
        public bool Login(string username, string userpwd)
        {
            string SQLString = "SELECT * " +
                               "FROM T_User " +
                               "WHERE UserID='" + username + "'" +
                               " AND UserPwd='" + userpwd + "'";
            DataSet ds = new DataSet();
            int i_result = C_DBAccess.DoSqlStr(SQLString, ref ds);
            if (ds.Tables[0].Rows.Count == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取所有需求信息
        /// </summary>
        /// <returns>返回需求信息结果集</returns>
        public DataSet GetInformation()
        {
            DataSet ds = new DataSet();
            string SQLString = "SELECT * FROM T_ExpBoardInfo ORDER BY id ASC";
            C_DBAccess.DoSqlStr(SQLString, ref ds);
            return ds;           
        }
        
        /// <summary>
        /// 获取用户权限和角色   
        /// </summary>
        /// <param name="jobnumber">用户账号</param>
        /// <param name="role">用于保存用户角色的对象</param>
        /// <returns>返回用户权限集合</returns>
        public DataSet GetUserPower(string jobnumber,ref string role)
        {                 
            DataSet ds = new DataSet();
            string SQLString = "SELECT RoleID FROM T_User WHERE UserID ='" + jobnumber + "'";
            string strRoleID = C_DBAccess.ExeSqlScalar(SQLString).ToString();
            SQLString = "select role from t_role where roleid = '" + strRoleID + "'";
            role = C_DBAccess.ExeSqlScalar(SQLString);
            SQLString = "select power from t_rolepower where role = (select role from t_role where roleid = '" + strRoleID + "')";
            C_DBAccess.DoSqlStr(SQLString, ref ds);            
            return ds;
        }

        /// <summary>
        /// 模糊查询方法
        /// </summary>
        /// <param name="strcondition">查询关键词</param>
        /// <returns>返回查询结果集</returns>
        public DataSet Query(string strcondition)
        {
             DataSet ds = new DataSet();
             string SQLString = "SELECT * FROM T_ExpBoardInfo WHERE productname like '%" + strcondition + "%' or process like '%" + strcondition + "%' or version like '%" + strcondition + "%' or state like '%" + strcondition + "%'";
             C_DBAccess.DoSqlStr(SQLString, ref ds);
             return ds;
        }

        /// <summary>
        /// 提交需求方法
        /// </summary>
        /// <param name="demand">需求对象</param>
        /// <returns>判断是否提交成功</returns>
        public bool CommitDemand(Demand demand)
        {
            string SQLString = "INSERT INTO T_ExpBoardInfo " +
               "(platform, productname, process, version, demandtime, remark, state) " +
               "VALUES ('" + demand.Platform +
               "', '" + demand.Productname +
               "', '" + demand.Process +
               "', '" + demand.Version +
               "', '" + demand.Demandtime +
               "', '" + demand.Remark +
               "', '" + demand.State + "')";
            if (C_DBAccess.DoSqlStr(SQLString) != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 用户注册
        /// </summary>
        /// <param name="user">用户对象</param>
        /// <returns>判断是否注册成功</returns>
        public bool UserRegist(User user)
        {
            string SQLString = "INSERT INTO T_USER VALUES('" + user.Uid + "','" + user.Pwd + "','" + user.Roleid + "')";
            if (C_DBAccess.DoSqlStr(SQLString) != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="uid">用户名</param>
        /// <param name="oldpwd">旧密码</param>
        /// <param name="newpwd">新密码</param>
        /// <returns>判断是否修改密码成功</returns>
        public bool ModifyPwd(string uid, string oldpwd, string newpwd)
        {
            string SQLString = "update t_user set userpwd='"+newpwd+"' where userid='"+uid+"' and userpwd='"+oldpwd+"'";
            if (C_DBAccess.DoSqlStr(SQLString) != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //获取服务器列表
        public DataSet GetServerList()
        {
            DataSet ds = new DataSet();            
            string SQLString = "select serveraddress from T_ExpSqlInfo";
            C_DBAccess.DoSqlStr(SQLString, ref ds);
            return ds;
        }

        //获取用户列表
        public DataSet GetUserList()
        {
            DataSet ds = new DataSet();
            string SQLString = "select userid from t_user";
            C_DBAccess.DoSqlStr(SQLString, ref ds);
            return ds;
        }

        //获取角色列表
        public DataSet GetRoleList()
        {
            DataSet ds = new DataSet();
            string SQLString = "select * from t_role";
            C_DBAccess.DoSqlStr(SQLString, ref ds);
            return ds;
        }

        //获取权限列表
        public DataSet GetPowerList()
        {
            DataSet ds = new DataSet();
            string SQLString = "select * from t_power";
            C_DBAccess.DoSqlStr(SQLString, ref ds);
            return ds;
        }

        //修改用户角色
        public bool UpdateUserRole(User user)
        {
            string SQLString = "update t_user set roleid='"+user.Roleid+"' where userid='"+user.Uid+"'";
            if (C_DBAccess.DoSqlStr(SQLString) != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //修改角色权限
        public bool UpdateRolePower(string role, List<string> powers)
        {
            bool flag = true;
            string SQLString = "delete from t_rolepower where role='" + role + "'";
            try
            {
                C_DBAccess.DoSqlStr(SQLString);
            }
            catch { }
            for (int i = 0; i < powers.Count; i++)
            {
                SQLString = "insert into t_rolepower values('" + role + "','" + powers[i] + "')";
                if (C_DBAccess.DoSqlStr(SQLString) != 0)
                {
                    continue;
                }
                else
                {
                    flag = false;
                    break;
                }
            }
            return flag;
        }

        //获取服务器信息
        public DataSet GetSqlInfo()
        {
            DataSet ds = new DataSet();
            string SQLString = "select * from T_ExpSqlInfo";
            C_DBAccess.DoSqlStr(SQLString, ref ds);
            return ds;
        }

        //获取服务器对应的数据库
        public DataSet GetDataBase(string server)
        {
            DataSet ds = new DataSet();
            string SQLString = "select DB from T_ExpSqlInfo where serveraddress='"+server+"'";
            C_DBAccess.DoSqlStr(SQLString, ref ds);
            return ds;
        }
    }
}
